package io.github.junxworks.qt.modules.tushare;

import java.io.File;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.commons.io.FilenameUtils;
import org.springframework.stereotype.Service;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import com.google.common.collect.Lists;

import io.github.junxworks.junx.core.util.StringUtils;

@Service("CreateTableService")
public class CreateTableService {

	public static void main(String[] args) throws Exception {
		createTable("C:\\Users\\AOC\\Desktop\\指数日线-d_stock_index_daily.txt");
	}

	public static void createTable(String filePath) throws Exception {
		File f = new File(filePath);
		SAXParserFactory factory = SAXParserFactory.newInstance();
		SAXParser saxParser = factory.newSAXParser();
		TableHandel handler = new TableHandel();
		saxParser.parse(f, handler);
		List<Column> columns = handler.getColumns();
		String name = FilenameUtils.getBaseName(f.getName());
		String[] tags = name.split("-");
		printCreateSQL(tags[1], tags[0], columns);
		printQueryCondition(columns);
	}

	private static void printCreateSQL(String tableName, String tableComment, List<Column> columns) {
		String body = StringUtils.join(columns.stream().flatMap(c -> {
			return Stream.of("`" + c.getName() + "` " + c.getType() + (StringUtils.notNull(c.getLength()) ? "(" + c.getLength() + ")" : "") + " DEFAULT NULL COMMENT '" + c.getRemark() + "'");
		}).collect(Collectors.toList()), ",");
		System.out.println("CREATE TABLE `" + tableName + "` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id'," + body + ",PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=624698 DEFAULT CHARSET=utf8 COMMENT='" + tableComment + "'");
	}

	private static void printQueryCondition(List<Column> columns) {
		System.out.println(StringUtils.join(columns.stream().flatMap(c -> {
			return Stream.of(c.getName());
		}).collect(Collectors.toList()), ","));
	}

	static class Column {
		private String name;

		private String type;

		private String length;

		private String remark;

		public String getName() {
			return name;
		}

		public void setName(String name) {
			this.name = name;
		}

		public String getType() {
			return type;
		}

		public void setType(String type) {
			this.type = type;
		}

		public String getLength() {
			return length;
		}

		public void setLength(String length) {
			this.length = length;
		}

		public String getRemark() {
			return remark;
		}

		public void setRemark(String remark) {
			this.remark = remark;
		}

	}

	static class TableHandel extends DefaultHandler {

		private boolean start = false;

		private Column column;

		private int index = 0;

		List<Column> columns = Lists.newArrayList();

		public List<Column> getColumns() {
			return columns;
		}

		public void setColumns(List<Column> columns) {
			this.columns = columns;
		}

		@Override
		public void startDocument() throws SAXException {
			System.out.println("sax解析开始");
		}

		//遍历xml文件结束标签
		@Override
		public void endDocument() throws SAXException {
			System.out.println("sax解析结束");
		}

		@Override
		public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
			if (qName.equals("tbody")) {
				start = true;
			} else if (start && qName.equals("tr")) {
				column = new Column();
				index = 0;
			} else if (qName.equals("td")) {

			}
		}

		@Override
		public void endElement(String uri, String localName, String qName) throws SAXException {
			if (start && qName.equals("tr")) {
				columns.add(column);
			}
		}

		@Override
		public void characters(char[] ch, int s, int length) throws SAXException {
			if (start && column != null) {
				String value = new String(ch, s, length).trim().toLowerCase();
				int i = index++;
				if (i == 1) {
					column.setName(value);
				} else if (i == 3) {
					if ("str".equals(value)) {
						column.setType("varchar");
						column.setLength("20");
					} else if ("float".equals(value)) {
						column.setType("double");
					} else if ("int".equals(value)) {
						column.setType("integer");
						column.setLength("10");
					}
				} else if (i == 7) {
					column.setRemark(value);
				}
			}
		}
	}

}
